#region Copyright 2006 - Evolution Tecnologia Ltda.
//
// Author: Rafael Franco Carvalho (rafael@Evolutiontec.com.br)
//
// All rights are reserved. Reproduction or transmission in whole or in part, in
// any form or by any means, eletronic, mechanical or otherwise, is prohibited
// without the prior written permission of the author.
//
// Filename: Database.cs
#endregion

using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;

namespace csharp.rad.general
{
    /// <summary>
    /// Database class
    /// </summary>
    public class Database
    {
        public enum Status { Successful, Failed };
        public struct Result
        {
            public Status Status;
            public String ErrorMessage;
        }                
        private String databaseName;
        private String databaseHost;
        private String databaseUser;
        private String databasePassword;
        private String databaseProtocol;
        private Boolean databaseConnected = false;
        public const Boolean doUseTransaction = true;
        public const Boolean doNotUseTransaction = false;
        //PostgreSQL
        private Npgsql.NpgsqlConnection databaseNpgSQLConnection;
        private Npgsql.NpgsqlTransaction npgSQLTransaction;
        //MS-SQL
        private System.Data.SqlClient.SqlConnection databaseMSSQLConnection;
        private System.Data.SqlClient.SqlTransaction msSQLTransaction;
        //MySQL
        private MySql.Data.MySqlClient.MySqlConnection databaseMySQLConnection;
        private MySql.Data.MySqlClient.MySqlTransaction mySQLTransaction;
        //Interbase/Firebird
        private FirebirdSql.Data.FirebirdClient.FbConnection databaseFBSQLConnection;
        private FirebirdSql.Data.FirebirdClient.FbTransaction fbSQLTransaction;

        /// <summary>
        /// Database name
        /// </summary>
        public String Name
        {
            get { return  databaseName; }
            set { databaseName = value; }
        }

        /// <summary>
        /// Database host (server name or ip address)
        /// </summary>
        public String Host
        {
            get { return  databaseHost; }
            set { databaseHost = value; }
        }

        /// <summary>
        /// Database user name used for connection
        /// </summary>
        public String User
        {
            get
            {
                return databaseUser;
            }
            set
            {
                databaseUser = value;
            }
        }

        /// <summary>
        /// Database user password used for connection
        /// </summary>
        public String Password
        {
            get
            {
                return databasePassword;
            }
            set
            {
                databasePassword = value;
            }
        }

        /// <summary>
        /// Database protocol, specify the database engine
        /// </summary>
        public String Protocol
        {
            get
            {
                return databaseProtocol;
            }
            set
            {
                databaseProtocol = value;
            }
        }

        /// <summary>
        /// The connection status
        /// </summary>
        public Boolean IsConnected
        {
            get
            {
                return databaseConnected;
            }
        }

        public String Info()
        {
            return databaseName + " (" + databaseHost + ")";
        }

        /// <summary>
        /// Connect to a database
        /// </summary>
        public Result Connect()
        {
            String connectionString;
            Result result = new Result();

            if (databaseConnected)
            {
                result.Status = Status.Successful;
                result.ErrorMessage = "";
                return  result;
            }
            else
            {
                //PostgreSQL
                if (databaseProtocol == "PostgreSQL")
                {
                	connectionString = "Server=" + getHost() + ";Port= " + getPort() +";User ID=" + databaseUser +
                                       ";Password=" + databasePassword + ";Database=" + databaseName+";CommandTimeout=20;";
                    if (databaseNpgSQLConnection == null)
                    {
                        databaseNpgSQLConnection = new Npgsql.NpgsqlConnection(connectionString);
            
                        try
                        {
                            databaseNpgSQLConnection.Open();
                        }
                        catch (Exception e)
                        {
                            result.Status = Status.Failed;
                            result.ErrorMessage = e.Message;
                            return result;
                        }
                        databaseConnected = true;
                    }
                    result.Status = Status.Successful;
                    result.ErrorMessage = "";
                    return result;
                }
                //Microsoft SQL-Server
                if (databaseProtocol == "MSSQL")
                {
                    connectionString = "Server=" + databaseHost + ";User ID=" + databaseUser +
                                       ";Password=" + databasePassword + ";Database=" + databaseName +
                                       ";Trusted_Connection=False";
                    if (databaseMSSQLConnection == null)
                    {
                        databaseMSSQLConnection = new System.Data.SqlClient.SqlConnection(connectionString);
                        try
                        {
                            databaseMSSQLConnection.Open();
                        }
                        catch (Exception e)
                        {
                            result.Status = Status.Failed;
                            result.ErrorMessage = e.Message;
                            return result;
                        }
                        databaseConnected = true;
                    }
                    result.Status = Status.Successful;
                    result.ErrorMessage = "";
                    return result;
                }
                //MySQL
                if (databaseProtocol == "MySQL")
                {
                    connectionString = "Server=" + databaseHost + ";User ID=" + databaseUser +
                                       ";Password=" + databasePassword + ";Database=" + databaseName +
                                       ";Allow Zero Datetime=true";
                    if (databaseMySQLConnection == null)
                    {
                        databaseMySQLConnection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
                        try
                        {
                            databaseMySQLConnection.Open();
                        }
                        catch (Exception e)
                        {
                            result.Status = Status.Failed;
                            result.ErrorMessage = e.Message;
                            return result;
                        }
                        databaseConnected = true;
                    }
                    result.Status = Status.Successful;
                    result.ErrorMessage = "";
                    return result;
                }
                //Firebird / Interbase
                if (databaseProtocol == "Firebird")
                {
                    connectionString = "DataSource=" + databaseHost + ";User=" + databaseUser +
                                       ";Password=" + databasePassword + ";Database=" + databaseName;
                    if (databaseFBSQLConnection == null)
                    {
                        databaseFBSQLConnection = new FirebirdSql.Data.FirebirdClient.FbConnection(connectionString);
                        try
                        {
                            databaseFBSQLConnection.Open();
                        }
                        catch (Exception e)
                        {
                            result.Status = Status.Failed;
                            result.ErrorMessage = e.Message;
                            return result;
                        }
                        databaseConnected = true;
                    }
                    result.Status = Status.Successful;
                    result.ErrorMessage = "";
                    return result;
                }

                result.Status = Status.Failed;
                result.ErrorMessage = "Database protocol is not specified";
                return result;
            }
        }

        /// <summary>
        /// Disconnect from a database
        /// </summary>
        public void Disconnect()
        {
            //PostgreSQL
            if (databaseProtocol == "PostgreSQL")
            {
                databaseNpgSQLConnection.Close();
            }
            //Microsoft SQL-Server
            if (databaseProtocol == "MSSQL")
            {
                databaseMSSQLConnection.Close();
            }
            //MySQL
            if (databaseProtocol == "MySQL")
            {
                databaseMySQLConnection.Close();
            }
            //Firebird/Interbase
            if (databaseProtocol == "Firebird")
            {
                databaseFBSQLConnection.Close();
            }
            databaseConnected = false;
        }

        public string getHost()
        {
			if( databaseHost.Contains(":"))
				return databaseHost.Split(':')[0];
			return databaseHost;
        }
        
        public string getPort()
        {
			if( databaseHost.Contains(":"))
				return databaseHost.Split(':')[1];
			return "5432";        	
        }
        
        /// <summary>
        /// Execute a SQL command like "Insert", "Update", "Delete" or "Alter"
        /// </summary>
        /// <param name="commandText">SQL command</param>
        /// <param name="useTransaction">Specify if should use transaction for this command</param>
        /// <returns>databaseResult struct</returns>
        public Result ExecuteCommand(String commandText, Boolean useTransaction)
        {
            Result result = new Result();

            //PostgreSQL
            if (databaseProtocol == "PostgreSQL")
            {
                Npgsql.NpgsqlCommand sqlCommand;

                if (useTransaction)
                {
                    BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                }
                sqlCommand = new Npgsql.NpgsqlCommand(commandText, databaseNpgSQLConnection, npgSQLTransaction);
                try
                {
                    sqlCommand.ExecuteNonQuery();
                    if (useTransaction)
                    {
                        CommitTransaction();
                    }
                    result.Status = Status.Successful;
                    result.ErrorMessage = "";
                    return result;
                }
                catch (Exception e)
                {
                    if (useTransaction)
                    {
                        RollBackTransaction();
                    }
                    result.Status = Status.Failed;
                    result.ErrorMessage = e.Message;
                    return result;
                }
            }
            //Microsoft SQL-Server
            if (databaseProtocol == "MSSQL")
            {
                System.Data.SqlClient.SqlCommand sqlCommand;

                if (useTransaction)
                {
                    BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                }
                sqlCommand = new System.Data.SqlClient.SqlCommand(commandText, databaseMSSQLConnection, msSQLTransaction);
                try
                {
                    sqlCommand.ExecuteNonQuery();
                    if (useTransaction)
                    {
                        CommitTransaction();
                    }
                    result.Status = Status.Successful;
                    result.ErrorMessage = "";
                    return result;
                }
                catch (Exception e)
                {
                    if (useTransaction)
                    {
                        RollBackTransaction();
                    }
                    result.Status = Status.Failed;
                    result.ErrorMessage = e.Message;
                    return result;
                }
            }
            //MySQL
            if (databaseProtocol == "MySQL")
            {
                MySql.Data.MySqlClient.MySqlCommand sqlCommand;

                if (useTransaction)
                {
                    BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                }
                sqlCommand = new MySql.Data.MySqlClient.MySqlCommand(commandText, databaseMySQLConnection, mySQLTransaction);
                try
                {
                    sqlCommand.ExecuteNonQuery();
                    if (useTransaction)
                    {
                        CommitTransaction();
                    }
                    result.Status = Status.Successful;
                    result.ErrorMessage = "";
                    return result;
                }
                catch (Exception e)
                {
                    if (useTransaction)
                    {
                        RollBackTransaction();
                    }
                    result.Status = Status.Failed;
                    result.ErrorMessage = e.Message;
                    return result;
                }
            }
            //Firebird/Interbase
            if (databaseProtocol == "Firebird")
            {
                FirebirdSql.Data.FirebirdClient.FbCommand sqlCommand;

                if (useTransaction)
                {
                    BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                }
                sqlCommand = new FirebirdSql.Data.FirebirdClient.FbCommand(commandText, databaseFBSQLConnection, fbSQLTransaction);
                try
                {
                    sqlCommand.ExecuteNonQuery();
                    if (useTransaction)
                    {
                        CommitTransaction();
                    }
                    result.Status = Status.Successful;
                    result.ErrorMessage = "";
                    return result;
                }
                catch (Exception e)
                {
                    if (useTransaction)
                    {
                        RollBackTransaction();
                    }
                    result.Status = Status.Failed;
                    result.ErrorMessage = e.Message;
                    return result;
                }
            }
            result.Status = Status.Failed;
            result.ErrorMessage = "Database protocol is not specified";
            return result;
        }
        
        /// <summary>
        /// Execute a SQL query like "Select"
        /// </summary>
        /// <param name="commandText">SQL query</param>
        /// <param name="tableName">Name of table used on dataset</param>
        /// <param name="result">databaseResult</param>
        /// <returns>Dataset</returns>
        public System.Data.DataSet ExecuteQuery(String commandText, String tableName, out Result result)
        {
            //PostgreSQL
            if (databaseProtocol == "PostgreSQL")
            {
                Npgsql.NpgsqlCommand sqlCommand;
                Npgsql.NpgsqlDataReader sqlDataReader;
                System.Data.DataSet sqlDataSet = new System.Data.DataSet();

                try
                {
                    sqlCommand = new Npgsql.NpgsqlCommand(commandText, databaseNpgSQLConnection, npgSQLTransaction);
                    sqlDataReader = sqlCommand.ExecuteReader();
                    sqlDataSet.Load(sqlDataReader, System.Data.LoadOption.OverwriteChanges, tableName);
                }
                catch (Exception e)
                {
                    result.Status = Status.Failed;
                    result.ErrorMessage = e.Message;
                    return null;
                }

                sqlDataReader.Close();
                sqlDataReader.Dispose();
                sqlCommand.Dispose();
                result.Status = Status.Successful;
                result.ErrorMessage = "";
                return sqlDataSet;
            }
            //Microsoft SQL-Server
            if (databaseProtocol == "MSSQL")
            {
                System.Data.SqlClient.SqlCommand sqlCommand;
                System.Data.SqlClient.SqlDataReader sqlDataReader;
                System.Data.DataSet sqlDataSet = new System.Data.DataSet();

                try
                {
                    sqlCommand = new System.Data.SqlClient.SqlCommand(commandText, databaseMSSQLConnection, msSQLTransaction);
                    sqlDataReader = sqlCommand.ExecuteReader();
                    sqlDataSet.Load(sqlDataReader, System.Data.LoadOption.OverwriteChanges, tableName);
                }
                catch (Exception e)
                {
                    result.Status = Status.Failed;
                    result.ErrorMessage = e.Message;
                    return null;
                }

                sqlDataReader.Close();
                sqlDataReader.Dispose();
                sqlCommand.Dispose();
                result.Status = Status.Successful;
                result.ErrorMessage = "";
                return sqlDataSet;
            }
            //MySQL
            if (databaseProtocol == "MySQL")
            {
                MySql.Data.MySqlClient.MySqlCommand sqlCommand;
                MySql.Data.MySqlClient.MySqlDataReader sqlDataReader;
                System.Data.DataSet sqlDataSet = new System.Data.DataSet();

                try
                {
                    sqlCommand = new MySql.Data.MySqlClient.MySqlCommand(commandText, databaseMySQLConnection, mySQLTransaction);
                    sqlDataReader = sqlCommand.ExecuteReader();
                    sqlDataSet.Load(sqlDataReader, System.Data.LoadOption.OverwriteChanges, tableName);
                }
                catch (Exception e)
                {
                    result.Status = Status.Failed;
                    result.ErrorMessage = e.Message;
                    return null;
                }

                sqlDataReader.Close();
                sqlCommand.Dispose();
                result.Status = Status.Successful;
                result.ErrorMessage = "";
                return sqlDataSet;
            }
            //Firebird/Interbase
            if (databaseProtocol == "Firebird")
            {
                FirebirdSql.Data.FirebirdClient.FbCommand sqlCommand;
                FirebirdSql.Data.FirebirdClient.FbDataReader sqlDataReader;
                System.Data.DataSet sqlDataSet = new System.Data.DataSet();

                try
                {
                    sqlCommand = new FirebirdSql.Data.FirebirdClient.FbCommand(commandText, databaseFBSQLConnection, fbSQLTransaction);
                    sqlDataReader = sqlCommand.ExecuteReader();
                    sqlDataSet.Load(sqlDataReader, System.Data.LoadOption.OverwriteChanges, tableName);
                }
                catch (Exception e)
                {
                    result.Status = Status.Failed;
                    result.ErrorMessage = e.Message;
                    return null;
                }

                sqlDataReader.Close();
                sqlCommand.Dispose();
                result.Status = Status.Successful;
                result.ErrorMessage = "";
                return sqlDataSet;
            }
            result.Status = Status.Failed;
            result.ErrorMessage = "Database protocol is not specified";
            return null;
        }
        
        /// <summary>
        /// Fetch one single value from a SQL query
        /// </summary>
        /// <param name="commandText">SQL query</param>
        /// <param name="result">databaseResult</param>
        /// <returns>Object</returns>
        public Object ExecuteScalar(String commandText, out Result result)
        {
            //PostgreSQL
            if (databaseProtocol == "PostgreSQL")
            {
                Npgsql.NpgsqlCommand sqlCommand;
                Object myResultObject;

                try
                {
                    sqlCommand = new Npgsql.NpgsqlCommand(commandText, databaseNpgSQLConnection, npgSQLTransaction);
                    myResultObject = sqlCommand.ExecuteScalar();
                }
                catch (Exception e)
                {
                    result.Status = Status.Failed;
                    result.ErrorMessage = e.Message;
                    return null;
                }

                sqlCommand.Dispose();
                result.Status = Status.Successful;
                result.ErrorMessage = "";
                return myResultObject;
            }
            //Microsoft SQL-Server
            if (databaseProtocol == "MSSQL")
            {
                System.Data.SqlClient.SqlCommand sqlCommand;
                Object myResultObject;

                try
                {
                    sqlCommand = new System.Data.SqlClient.SqlCommand(commandText, databaseMSSQLConnection, msSQLTransaction);
                    myResultObject = sqlCommand.ExecuteScalar();
                }
                catch (Exception e)
                {
                    result.Status = Status.Failed;
                    result.ErrorMessage = e.Message;
                    return null;
                }

                sqlCommand.Dispose();
                result.Status = Status.Successful;
                result.ErrorMessage = "";
                return myResultObject;
            }
            //MySQL
            if (databaseProtocol == "MySQL")
            {
                MySql.Data.MySqlClient.MySqlCommand sqlCommand;
                Object myResultObject;

                try
                {
                    sqlCommand = new MySql.Data.MySqlClient.MySqlCommand(commandText, databaseMySQLConnection, mySQLTransaction);
                    myResultObject = sqlCommand.ExecuteScalar();
                }
                catch (Exception e)
                {
                    result.Status = Status.Failed;
                    result.ErrorMessage = e.Message;
                    return null;
                }

                sqlCommand.Dispose();
                result.Status = Status.Successful;
                result.ErrorMessage = "";
                return myResultObject;
            }
            //Firebird/Interbase
            if (databaseProtocol == "Firebird")
            {
                FirebirdSql.Data.FirebirdClient.FbCommand sqlCommand;
                Object myResultObject;

                try
                {
                    sqlCommand = new FirebirdSql.Data.FirebirdClient.FbCommand(commandText, databaseFBSQLConnection, fbSQLTransaction);
                    myResultObject = sqlCommand.ExecuteScalar();
                }
                catch (Exception e)
                {
                    result.Status = Status.Failed;
                    result.ErrorMessage = e.Message;
                    return null;
                }

                sqlCommand.Dispose();
                result.Status = Status.Successful;
                result.ErrorMessage = "";
                return myResultObject;
            }
            result.Status = Status.Failed;
            result.ErrorMessage = "Database protocol is not specified";
            return null;
        }

        /// <summary>
        /// Retrive next serial number of the "serialName"
        /// </summary>
        /// <param name="serialName">Name of the serial variable on the database</param>
        /// <returns>Int32</returns>
        public Int32 GetSerial(String serialName)
        { 
            Result databaseResult = new Result();
            //PostgreSQL
            if (databaseProtocol == "PostgreSQL")
            {
                return System.Convert.ToInt32(ExecuteScalar("SELECT nextval('" + serialName + "') AS codigo_serial", out databaseResult));
            }            
            //Firebird/Interbase
            if (databaseProtocol == "Firebird")
            {
                return System.Convert.ToInt32(ExecuteScalar("SELECT GEN_ID('" + serialName + "',1) AS codigo_serial FROM RDB$DATABASE", out databaseResult));
            }
            return 0;
        }

        /// <summary>
        /// Begin a database transaction
        /// </summary>
        /// <param name="databaseIsolationLevel">Specify the transaction isolation level of this transaction</param>
        public void BeginTransaction(System.Data.IsolationLevel databaseIsolationLevel)
        { 
            //PostgreSQL
            if ((databaseProtocol == "PostgreSQL") && (npgSQLTransaction == null))
            {
                npgSQLTransaction = databaseNpgSQLConnection.BeginTransaction(databaseIsolationLevel);
            }
            //MSSQL
            if ((databaseProtocol == "MSSQL") && (msSQLTransaction == null))
            {
                msSQLTransaction = databaseMSSQLConnection.BeginTransaction(databaseIsolationLevel);
            }
            //MySQL
            if ((databaseProtocol == "MySQL") && (mySQLTransaction == null))
            {
                mySQLTransaction = databaseMySQLConnection.BeginTransaction(databaseIsolationLevel);
            }
            //Firebird/Interbase
            if ((databaseProtocol == "Firebird") && (fbSQLTransaction == null))
            {
                fbSQLTransaction = databaseFBSQLConnection.BeginTransaction(databaseIsolationLevel);
            }
        }

        /// <summary>
        /// Commit an opened database transaction
        /// </summary>
        public void CommitTransaction()
        {
            //PostgreSQL
            if (databaseProtocol == "PostgreSQL")
            {
                npgSQLTransaction.Commit();
                npgSQLTransaction = null;
            }
            //MSSQL
            if (databaseProtocol == "MSSQL")
            {
                msSQLTransaction.Commit();
                msSQLTransaction = null;
            }
            //MySQL
            if (databaseProtocol == "MySQL")
            {
                mySQLTransaction.Commit();
                mySQLTransaction = null;
            }
            //Firebird/Interbase
            if (databaseProtocol == "Firebird")
            {
                fbSQLTransaction.Commit();
                fbSQLTransaction = null;
            }
        }

        /// <summary>
        /// Rollback an opened database transaction
        /// </summary>
        public void RollBackTransaction()
        {
            //PostgreSQL
            if (databaseProtocol == "PostgreSQL")
            {
                npgSQLTransaction.Rollback();
                npgSQLTransaction = null;
            }
            //MSSQL
            if (databaseProtocol == "MSSQL")
            {
                msSQLTransaction.Rollback();
                msSQLTransaction = null;
            }
            //MySQL
            if (databaseProtocol == "MySQL")
            {
                mySQLTransaction.Rollback();
                mySQLTransaction = null;
            }
            //Firebird/Interbase
            if (databaseProtocol == "Firebird")
            {
                fbSQLTransaction.Rollback();
                fbSQLTransaction = null;
            }
        }
    
        public Npgsql.NpgsqlConnection getConnection()
        {
        	return this.databaseNpgSQLConnection;
        }
    }
}
